INNER JOIN
In this lesson, we will study the INNER JOIN in SQL.
We'll cover the following
INNER JOIN#
The INNER JOIN keyword selects records that have matching values in both tables.
Syntax#
The basic syntax of the INNER JOIN is as follows:
SELECT table1.column1, table2.column2 ...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;
Example#
We will be using the CUSTOMERS and ORDERS tables as defined in the previous lesson.
Let’s say we want to retrieve the information of only those customers that have placed an order. This can be done by joining the two tables:
The following code will show you how to join the two tables:
Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are records in the “Orders” table that do not have matches in “Customers”, these orders will not be shown.
That is why we don’t see Emily, Bill or Jane in the result-set; they have not placed any orders.
Quick quiz!#
Which of the following queries will return the NAME and AGE of a customer along with the DATE they placed an order?
A)
SELECT CUSTOMERS.NAME, CUSTOMERS.AGE, ORDERS.DATE
FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
B)
SELECT NAME, AGE, DATE
FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
C)
SELECT CUSTOMERS.NAME, CUSTOMERS.AGE, ORDERS.DATE
FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
D)
SELECT CUSTOMERS.NAME, CUSTOMERS.AGE, ORDERS.DATE
FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.NAME = ORDERS.CUSTOMER_ID;
In the next lesson, we will take a look at the LEFT JOIN keyword.
